﻿#region - Class details -
/*  
 *  Developer   : Wicus Botha
 *  Description : 
 *  History     : WB - 2011-12-19 - Created
 */
#endregion

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using IQ.Base.Services;

namespace SqlServerAdmin
{
    public static class SqlQueries
    {
        public const short IndexStatusPk = 2048;
        public const short IndexStatusUnique = 2;
        // Sysobject Types
        public const string SoT_DefaultValues = "D"; // Default values
        public const string SoT_SystemTables = "S"; // System tables
        public const string SoT_Tables = "U"; // User tables
        public const string SoT_Triggers = "TR"; // Triggers
        public const string SoT_StoreProc = "P"; // Store procedure
        public const string SoT_ForeignKey = "F"; // Forgein key / Relationships
        public const string SoT_PrimaryKey = "PK"; // Primary key
        public const string SoT_FunctionsAll = "F*"; // User functions
        public const string SoT_Functions = "FN"; // User functions
        public const string SoT_FunctionsAsm = "FS"; // User functions assemblies
        public const string SoT_UniqueIndex = "UQ"; // Unique index
        public const string SoT_View = "V"; // Views
        //
        public const string Field_Id = "Id";
        public const string Field_Name = "Name";
        public const string Field_Type = "Type";
        public const string Field_xType = "xType";
        public const string Field_TableId = "TableId";
        public const string Field_TableName = "TableName";
        public const string Field_ObjectText = "ObjectText";
        public const string Field_IndexId = "IndexId";
        public const string Field_IndexKeyNo = "IndexKeyNo";
        public const string Field_IndexName = "IndexName";
        public const string Field_IndexStatus = "Status";
        public const string Field_ColumnId = "ColumnId";
        public const string Field_ColumnName = "FieldName";
        public const string Field_ColumnLength = "Length";
        public const string Field_ColumnPrecision = "Precision";
        public const string Field_ColumnScale = "Scale";
        public const string Field_ColumnIsIdentity = "IsIdentity";
        public const string Field_ColumnIsNullable = "IsNullable";
        public const string Field_ColumnFieldType = "FieldType";
        public const string Field_ColumnDefaultValue = "DefaultValue";
        public const string Field_ColumnOrder = "ColOrder";
        //
        #region - Private variables -
        private const string sqlSysObj = "SELECT name as Name,id as Id FROM sysobjects";
        #endregion
        //
        public const string SqlDataBases = "EXEC sp_databases";
        public const string SqlTables = sqlSysObj + " WHERE xtype in ('U') ORDER BY name";
        public const string SqlTablesDetail =
                    "SELECT cast(OBJ.name as varchar(50)) as TableName, OBJ.id,IND.rowcnt as RowsCount,"
                    + " ISNULL(IND.dpages,0) * 8 as TableSizeKB,(ISNULL(IND.used,0)*8) - (ISNULL(IND.dpages,0)*8) as IndexSizeKB"
                    + " FROM sysobjects OBJ LEFT JOIN sysindexes IND ON OBJ.id=IND.id and IND.indid in (0,1)" //255
                    + " WHERE OBJ.xtype in ('U')";
        public const string SqlViews = sqlSysObj + " WHERE xtype='V' AND category <> 2 ORDER BY Name";
        public const string SqlProcedures = sqlSysObj + " WHERE xtype='P' AND category <> 2 ORDER BY Name";
        public const string SqlProcDetail = "SELECT text AS ObjectText FROM syscomments WHERE ID = ";
        public const string SqlActiveConnections = "SELECT SPS.SPID,SPS.dbid,SDB.Name as DBName," +
                    " SPS.program_name,SPS.loginame,SPS.hostname,SPS.last_batch,SPS.status,SPS.cmd" +
                    " FROM Master.dbo.SysProcesses SPS LEFT JOIN Master.dbo.SysDatabases SDB on SPS.dbid=SDB.dbid";
        //
        public const string SqlProcParams =
                    "SELECT SO.Name AS StoreProcName, SO.Type_Desc AS ObjectType, PM.Parameter_ID AS ParameterId," +
                        "CASE WHEN PM.Parameter_ID = 0 THEN 'Returns' ELSE PM.Name END AS ParameterName," +
                            " TYPE_NAME(PM.User_Type_ID) AS ParameterDataType," +
                        "CASE WHEN TYPE_NAME(PM.User_Type_ID) IN ('float', 'uniqueidentifier', 'datetime', 'bit', 'bigint', 'int', 'image', 'money', " +
                                "'xml', 'varbinary', 'tinyint', 'text', 'ntext', 'smallint', 'smallmoney') THEN ''" +
                        "WHEN TYPE_NAME(PM.User_Type_ID) IN ('decimal', 'numeric') THEN '(' + CAST( Precision AS VARCHAR(4) ) + ', ' + CAST( Scale AS VARCHAR(4)) + ')'" +
                        "ELSE CAST( PM.Max_Length AS VARCHAR(4)) END AS Size," +
                        "CASE WHEN PM.Is_Output = 1 THEN 'Output' ELSE 'Input' END AS Direction" +
                    "FROM sys.objects AS SO" +
                        "INNER JOIN sys.parameters AS PM ON PM.OBJECT_ID=SO.OBJECT_ID" +
                    "WHERE TYPE IN ('P','FN') ORDER BY SO.Name, PM.parameter_id";
        // Maintenance queries
        public const string SqlServerDrives = "EXECUTE master.dbo.xp_availablemedia";
        public const string SqlServerCommands = "SELECT command,s.text,start_time,percent_complete, " +
                    " CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), ' " +
                    "   + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, ' " +
                    "   + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time, " +
                    " CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), ' " +
                    "   + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, ' " +
                    "   + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go, " +
                    " dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time  " +
                    " FROM sys.dm_exec_requests r " +
                    " CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s " +
                    " WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')";
        //
        public static string GetSqlTableFieldList(string objectName)
        {
            return "SELECT name AS FieldName from syscolumns where id = OBJECT_ID('" + objectName + "')";
        }
        public static string GetSqlTableFields(string objectName)
        {
            return "SELECT SCL.id AS TableId,SCL.colid AS ColumnId,SCL.Colorder,SCL.Name AS FieldName,SCL.length AS Length, SCL.prec AS Precision," +
                    "  SCL.scale AS Scale,SCL.colstat AS IsIdentity,SCL.cdefault,SCL.isnullable AS IsNullable,STP.Name as FieldType,SCM.text as DefaultValue" +
                    " FROM syscolumns SCL" +
                    "     LEFT JOIN systypes STP ON SCL.xtype=STP.xtype" +
                    "     LEFT JOIN syscomments SCM ON SCL.cdefault=SCM.id" +
                    " WHERE SCL.id =  OBJECT_ID('" + objectName + "') " +
                    " ORDER BY SCL.id, SCL.Colorder";
        }
        public static string GetSqlTableKeys(string objectName)
        {
            return "SELECT IND.indid AS IndexId,IND.name AS IndexName,IND.Status,IND.rowcnt as RowsCount,"
                    + "ISNULL(IND.dpages,0) * 8 as TableSizeKB,(ISNULL(IND.used,0)*8) - (ISNULL(IND.dpages,0)*8) as IndexSizeKB"
                    + " FROM sysindexes IND"
                    + " WHERE IND.id = OBJECT_ID('" + objectName + "') AND (IND.Status & 8388704)= 0"
                    + " ORDER BY IND.id, IND.indid";
        }
        public static string GetSqlTableKeysFields(string objectName)
        {
            return "SELECT IND.id AS TableId,IND.indid AS IndexId,IND.keyno AS IndexKeyNo,COL.name AS FieldName"
                    + " FROM sysindexkeys IND LEFT JOIN syscolumns COL ON IND.id = COL.id AND IND.colid = COL.colid"
                    + (!String.IsNullOrEmpty(objectName) ? " WHERE IND.id = OBJECT_ID('" + objectName + "') " : "")
                    + " ORDER BY 1,2,3";
        }
        public static string GetSqlTableIndexes(string tblName)
        {
            return "SELECT TBL.id AS TableId,TBL.name as TableName,IND.indid AS IndexId,IND.name AS IndexName,IND.Status" +
                " FROM sysindexes IND LEFT JOIN sysobjects TBL ON TBL.id=IND.id" +
                " WHERE IND.id = OBJECT_ID('" + tblName + "') AND (IND.Status & 8388704)= 0" +
                " ORDER BY IND.id, IND.indid";
        }
        public static string GetSqlIndexFields(string tblName, string indexId)
        {
            return "SELECT IND.keyno AS IndexKeyNo,COL.name AS FieldName" +
                " FROM sysindexkeys IND LEFT JOIN syscolumns COL ON IND.id = COL.id AND IND.colid = COL.colid" +
                " WHERE IND.id = OBJECT_ID('" + tblName + "') AND IND.indid=" + indexId + "ORDER BY 1";
        }
        #region -- Meta Data Queries
        public static string GetSqlMdSystemObjects(string type)
        {
            return "SELECT name AS Name,id AS Id,xtype,parent_obj AS ParentId,type AS Type FROM sysobjects WHERE Type IN ('" 
                + type + "') ORDER BY Name";
        }

        public static string GetSqlMdSystemObjects()
        {
            return "SELECT name AS Name,id AS Id,xtype,parent_obj AS ParentId,type AS Type FROM sysobjects WHERE Type IN ('U','P','V','F','FN','FS') ORDER BY Name";
        }
        public static string GetSqlMdTableFields()
        {
            return "SELECT SCL.id AS TableId,SCL.colid AS ColumnId,SCL.Name AS FieldName,SCL.length AS Length, SCL.prec AS Precision," +
                    "  SCL.scale AS Scale,SCL.colstat AS IsIdentity,SCL.cdefault,SCL.isnullable AS IsNullable,STP.Name as FieldType,SCM.text as DefaultValue" +
                    " FROM syscolumns SCL" +
                    "     LEFT JOIN systypes STP ON SCL.xtype=STP.xtype" +
                    "     LEFT JOIN syscomments SCM ON SCL.cdefault=SCM.id" +
                    " WHERE SCL.id in (SELECT id FROM sysobjects WHERE TYPE in ('U'))" +
                    " ORDER BY SCL.id,SCL.Colorder";
        }
        // Store Procedures, Views & Functions
        public static string GetSqlMd_SVF()
        {
            return "SELECT SYO.Name,SYO.ID AS Id,SYO.xtype AS Type,SYC.text AS ObjectText FROM sysobjects SYO INNER JOIN syscomments SYC ON SYO.ID=SYC.ID" +
                    " WHERE SYO.Category=0 ORDER BY SYO.Name";
        }
        public static string GetSqlMdTableKeys()
        {
            return "SELECT TBL.id AS TableId,TBL.name as TableName,IND.indid AS IndexId,IND.name AS IndexName,IND.status AS Status" +
                    " FROM sysindexes IND LEFT JOIN sysobjects TBL ON TBL.id=IND.id" +
                    " WHERE TBL.xtype='U' AND (IND.Status & 8388704)= 0	AND IND.name IS NOT NULL ORDER BY TBL.name, IND.indid";
        }
        public static string GetSqlMdTableKeysFields()
        {
            return GetSqlTableKeysFields("");
        }
        #endregion
        public static string GetSqlServerDirectory(string path)
        {
            return "EXECUTE master.dbo.xp_dirtree N'" + path + "', 1, 1";
        }
        public static string GetSqlDatabaseId(string dbName)
        {
            return "SELECT dbid FROM sysdatabases WHERE name='" + dbName + "'";
        }
        public static string GetSqlPercentComplete(int dbid)
        {
            return "SELECT percent_complete FROM sys.dm_exec_requests (NOLOCK)" +
                    " WHERE database_id=" + dbid + " AND command in ('RESTORE DATABASE','BACKUP DATABASE','RESTORE LOG','BACKUP LOG')";
        }
        public static string GetAciveConnectionsForApp(string appName)
        {
            return "SELECT SPS.SPID FROM Master.dbo.SysProcesses SPS WHERE SPS.program_name='" + appName + "'";
        }
        //
    }	
}
